/**
 * @description: 定义班级 service
 * @version: 0.0.1
 * @author: z..
 * @Date: 2023-06-12 18:16:39
 */

import { Provide } from '@midwayjs/core';
import { UserRecords } from './user_records.entity';

import { InjectRepository, InjectDataSource } from '@midwayjs/sequelize';
import { Repository, Sequelize } from 'sequelize-typescript';

@Provide()
export class UserRecordsModel {

  @InjectRepository(UserRecords)
  UserRecordsRepository: Repository<UserRecords>;

  @InjectDataSource()
  SequelizeQuery: Sequelize;

  /**
   * 获取所用用户信息
   * @returns 
   */
  async getAllUserLists(searchKey: any): Promise<any> {
    let sql = `SELECT id, nick, union_id, avatar_url, email FROM user_records`;
    if (searchKey) {
      sql = `SELECT id, nick, union_id, avatar_url, email FROM user_records WHERE nick LIKE '%${searchKey}%';`;
    }
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  };

  /**
  * 根据id 获取用户信息
  * @param {*} id 
  * @returns 
  */
  async getUserById(id: number): Promise<any> {
    const sql = `SELECT
      t1.id,
      t1.nick,
      t1.union_id,
      t1.user_id,
      t1.avatar_url,
      t1.open_id,
      t1.state_code,
      t1.access_token,
      t1.refresh_token,
      t1.expires_time,
      t1.dept_id,
      t1.contact_type,
      t1.dept_id_list,
      t1.is_admin,
      t1.created_at,
      t1.updated_at,
      t1.dept_name_list,
      t1.user_status,
      t1.user_title,
      t1.hours_amount,
      t1.email,
      t3.role_name,
      t3.role_code
    FROM
      user_records as t1 
      LEFT JOIN user_role_contact as t2 ON t1.id = t2.user_id
      LEFT JOIN user_role_records as t3 ON t3.id = t2.role_id
    WHERE
      t1.id = ${id};`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  };
  /**
   * 根据unionId 获取用户信息
   * @param {*} unionId 
   * @returns 
   */
  async getUserUnionId(unionId: string): Promise<any> {
    const sql = `SELECT
      id,
      nick,
      union_id,
      user_id,
      avatar_url,
      open_id,
      dept_id,
      contact_type,
      dept_id_list,
      is_admin,
      dept_name_list,
      user_status,
      hours_amount,
      email
    FROM
      user_records 
    WHERE
      union_id = '${unionId}';`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  };

  /**
   * 根据unionId 获取用户信息和角色信息
   * @param {*} unionId 
   * @returns 
   */
  async getUserUnionIdAndRole(unionId: string): Promise<any> {
    const sql = `SELECT
      t1.id,
      t1.nick,
      t1.union_id,
      t1.user_id,
      t1.avatar_url,
      t1.open_id,
      t1.dept_id,
      t1.contact_type,
      t1.dept_id_list,
      t1.is_admin,
      t1.dept_name_list,
      t1.user_status,
      t1.hours_amount,
      t1.email,
      t3.role_name,
      t3.role_code
    FROM
      user_records as t1 
      LEFT JOIN user_role_contact as t2 ON t1.id = t2.user_id
      LEFT JOIN user_role_records as t3 ON t3.id = t2.role_id
    WHERE
      t1.union_id = '${unionId}';`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  };

  /**
   * 新增用户信息
   * @param {*} params 
   * @returns 
   */
  async insertUserInfo(params: any): Promise<any> {
    const sql = `INSERT INTO user_records 
      SET  nick = '${params.nick}',
        union_id = '${params.unionId}',
        avatar_url = '${params.avatarUrl}',
        open_id = '${params.openId}',
        access_token = '${params.accessToken}',
        refresh_token = '${params.refreshToken}',
        expires_time = '${params.expiresTime}',
        contact_type = '${params.contactType}',
        user_id = '${params.userid}',
        email='${params.email || ""}',
        created_at = now(),
        updated_at = now();`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }

  //	根据登录名获取 账户信息
  async getDataByToken(access_token: string): Promise<any> {
    const sql = `SELECT id,nick,union_id,user_id,email from user_records WHERE access_token = '${access_token}';`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }

  /**
   * 更新用户数据
   * @param {*} id 
   * @param {*} userId 
   * @param {*} accessToken 
   * @param {*} refreshToken 
   * @param {*} expiresTime 
   * @param {*} contactType 
   * @returns 
   */
  async updateUserInfo(params: any): Promise<any> {
    const sql = `UPDATE user_records 
    SET user_id = '${params.userId}',
      access_token = '${params.accessToken}',
      refresh_token = '${params.refreshToken}',
      expires_time = '${params.expiresTime}',
      contact_type = '${params.contactType}',
      email='${params.email || ""}',
      updated_at = now()
    WHERE
      id = ${params.id};`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }

  /**
   * 更新用户详细信息
   * @param {*} id 
   * @param {*} nick 
   * @param {*} deptIdList 
   * @param {*} isAdmin 
   * @param {*} otherInfo
   * @param {*} userTitle
   * @returns 
   */
  async updateUserDetails(params: any): Promise<any> {
    const sql = `UPDATE user_records 
    SET 
      nick = '${params.nick}',
      dept_id_list = '${params.deptIdList}',
      is_admin = ${params.isAdmin},
      other_info = '${params.otherInfo}',
      user_title = '${params.userTitle}',
      dept_name_list = '${params.deptNameList}',
      updated_at = now()
    WHERE
      id = ${params.id};`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }


  /**
   * 分页查询获取总条数
   * @param {*} searchKey   搜索信息
   * @returns 
   */
  async getTotalNum(searchKey: any): Promise<any> {
    let sql = `SELECT count(1) as num from user_records;`;
    if (searchKey) {
      sql = `SELECT count(1) AS num FROM user_records WHERE nick LIKE '%${searchKey}%';`;
    }
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }

  /**
   * 分页查询获取列表
   * @param {*} pageNum 
   * @param {*} pageSize 
   * @param {*} searchKey 
   * @returns 
   */
  async getDataByPage(pageNum:number, pageSize:number, searchKey:any): Promise<any> {
    const _limit = (pageNum - 1) * pageSize;
    let sql = `SELECT t1.id,t1.nick,t1.avatar_url,t1.dept_id_list,t1.is_admin,t1.created_at,t1.updated_at,t1.dept_name_list,t1.user_status,t1.user_title,t1.hours_amount,t3.role_name,t3.role_code
    FROM user_records as t1 
      LEFT JOIN user_role_contact as t2 ON t1.id = t2.user_id
      LEFT JOIN user_role_records as t3 ON t3.id = t2.role_id
    ORDER BY t1.created_at DESC LIMIT ${_limit},${pageSize};`;

    if (searchKey) {
      sql = `SELECT t1.id,t1.nick,t1.avatar_url,t1.dept_id_list,t1.is_admin,t1.created_at,t1.updated_at,t1.dept_name_list,t1.user_status,t1.user_title,t1.hours_amount,t3.role_name,t3.role_code
    FROM user_records as t1
      LEFT JOIN user_role_contact as t2 ON t1.id = t2.user_id
      LEFT JOIN user_role_records as t3 ON t3.id = t2.role_id
    WHERE t1.nick LIKE '%${searchKey}%' ORDER BY t1.created_at DESC LIMIT ${_limit},${pageSize};`;
    }
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }


  /**
   * 更新用户信息
   * @param {*} id 
   * @param {*} key 
   * @param {*} value
   * @returns 
   */
  async updateUserById(params: { id: number, key: any, value: any }): Promise<any> {
    const sql = `UPDATE user_records SET ${params.key} = ${params.value}, updated_at = now() WHERE id = ${params.id};`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }

  /**
   * 更新用户工时金额
   * @param params 
   */
  async updateUserHoursAmount(params: { id: number, hoursAmount: number}): Promise<any> {
    const sql = `UPDATE user_records SET hours_amount = ${params.hoursAmount}, updated_at = now() WHERE id = ${params.id};`;
    const [results] = await this.SequelizeQuery.query(sql);
    return results;
  }

}